using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TestSystem.DAL.TestSystem
{
    public class TestDataAccess : DataAccess
    {
        public static IDataReader GetTestsByQuizID(int quizID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Test_GetByQuizID", paramsArray);
        }

        public static IDataReader GetTestsByQuizID(int quizID, int lowerBound, int upperbound, int complexityFilter, int typeFilter)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@LowerBound", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, lowerBound),
                    new SqlParameter("@UpperBound", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, upperbound),
                    new SqlParameter("@ComplexityFilter", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, complexityFilter),
                    new SqlParameter("@TypeFilter", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, typeFilter)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Test_GetByQuizID", paramsArray);
        }

        public static int InsertTest(int quizID, string body, int testOrder, int complexity, int testType)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@Body", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, body),
                    new SqlParameter("@TestOrder", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testOrder),
                    new SqlParameter("@Complexity", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, complexity),
                    new SqlParameter("@TestType", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testType)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Test_Insert", paramsArray));
        }

        public static int InsertTest(SqlTransaction tran, int quizID, string body, int testOrder, int complexity, int testType)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@Body", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, body),
                    new SqlParameter("@TestOrder", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testOrder),
                    new SqlParameter("@Complexity", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, complexity),
                    new SqlParameter("@TestType", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testType)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(tran, CommandType.StoredProcedure, "Test_Insert", paramsArray));
        }

        public static void DeleteByQuizID(int quizID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Test_DeleteByQuizID", paramsArray);
        }

        public static int GetCountByQuizID(int quizID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Test_GetCountByQuizID", paramsArray));
        }

        public static int GetCountByQuizID(int quizID, int comlexityFilter, int typeFilter)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@ComplexityFilter", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, comlexityFilter),
                    new SqlParameter("@TypeFilter", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, typeFilter)
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Test_GetCountByQuizID", paramsArray));
        }
        
        public static void Update(int testID, int quizID, string body, int testOrder, int complexity, int testType)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@TestID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testID),
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@Body", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, body),
                    new SqlParameter("@TestOrder", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testOrder),
                    new SqlParameter("@Complexity", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, complexity),
                    new SqlParameter("@TestType", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testType)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Test_Update", paramsArray);
        }

        public static void Delete(int testID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@TestID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testID)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Test_Delete", paramsArray);
        }

        public static IDataReader GetAll()
        {
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Test_GetAll");
        }

        public static IDataReader GetByTestID(int testID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@TestID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, testID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Test_GetByTestID", paramsArray);
        }

        public static int GetCount()
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Test_GetCount"));
        }
    }
}
